package com.hd123.rumba.qt.def;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.hd123.rumba.exception.BusinessException;
import com.hd123.rumba.qt.QTConstants;
import com.hd123.rumba.util.StringUtil;

public class SQLCriteria {
  /**
   * @return 查询条件名称, 用作显示
   */
  public String getDisplayName() throws BusinessException {
    return query.getTitleByColumn(getLeft()) + query.getOperatorName(getOperator());
  }

  /**
   * 返回适用于where或having的条件表达式
   * 
   * @param expressionType
   *          取值SQLQuery.WHERECLAUSE|HAVINGCLAUSE
   * @param idx
   * @return
   */
  public String getSQLExpression(int expressionType, int idx) throws BusinessException {
    StringBuffer sb = new StringBuffer();

    if (idx >= 0 && idx < getRights().size() && getRights().get(idx) != null
        && ((String) getRights().get(idx)).trim().length() > 0) {
      String tleft = query.translateColumn(getLeft());
      String s = (String) getRights().get(idx);

      if (s.startsWith("\\\\"))
        s = s.substring(1);
      else if (s.startsWith("\\")) {
        if (s.toUpperCase().equals("\\NULL")) {
          sb.append(tleft);
          sb.append(" IS NULL");
        } else {
          sb.append(tleft);
          sb.append(" ");
          sb.append(operator);
          sb.append(" ");
          sb.append(toLiteral(s.substring(1)));
        }
        return sb.length() == 0 ? null : sb.toString();
      }

      if (operator.equals("BETWEEN") || operator.equals("NOT BETWEEN")) {
        int i = s.indexOf(",");
        sb.append(tleft);
        sb.append(" ");
        sb.append(operator);
        sb.append(" ");
        sb.append(toLiteral(s.substring(0, i)));
        sb.append(" and ");
        sb.append(toLiteral(s.substring(i + 1)));
      } else if (operator.equals("IN") || operator.equals("NOT IN")) {
        sb.append(tleft);
        sb.append(" ");
        sb.append(operator);
        sb.append(" (");
        int i = s.indexOf(",");
        while (i > 0) {
          sb.append(toLiteral(s.substring(0, i)));
          sb.append(",");
          s = s.substring(i + 1);
          i = s.indexOf(",");
        }
        sb.append(toLiteral(s));
        sb.append(")");
      } else if (operator.equals("LIKE") || operator.equals("NOT LIKE")) {
        if (s.indexOf("%") < 0 && s.indexOf("_") < 0)
          s = s + "%";
        sb.append(tleft);
        sb.append(" ");
        sb.append(operator);
        sb.append(" ");
        sb.append(toLiteral(s));
      } else if (operator.equals("*=") && "ORACLE".equals(query.DBMS)) {
        sb.append(tleft);
        sb.append("(+)=");
        sb.append(toLiteral(s));
      } else if (operator.equals("=")) {
        sb.append(ScopeExpression.scopeExpToSQLExp(tleft, s, isQuoted()));
      } else {
        sb.append(tleft);
        sb.append(" ");
        sb.append(operator);
        sb.append(" ");
        sb.append(toLiteral(s));
      }
    }
    if (expressionType == QueryDefinition.WHERECLAUSE && !isHaving()
        || expressionType == QueryDefinition.HAVINGCLAUSE && isHaving())
      return sb.length() == 0 ? null : sb.toString();
    else
      return null;
  }

  /**
   * 根据isQuoted属性为s加引号
   * 
   * @param s
   * @return
   */
  private String toLiteral(String s) {
    if (isQuoted())
      s = "'" + s.replace("'", "''") + "'";
    return s;
  }

  public String debugInfo() {
    StringBuffer sb = new StringBuffer();
    sb.append(getClass().getName() + "\n");
    sb.append("Left: " + getLeft() + "\n");
    sb.append("Operator: " + getOperator() + "\n");
    sb.append("Rights: " + getRights() + "\n");
    sb.append("Having: " + isHaving() + "\n");
    sb.append("Quoted: " + isQuoted() + "\n");
    sb.append("PickNames: " + getPickNames() + "\n");
    sb.append("PickValues: " + getPickValues() + "\n");
    sb.append("DefaultValue: " + getDefaultValue() + "\n");
    sb.append("Required: " + isRequired() + "\n");
    return sb.toString();
  }

  private String left;

  private String operator;

  private List rights = new ArrayList(); // List of String

  private boolean having;

  /**
   * 0-字符 1-数字 2-日期
   */
  private int quoted;

  private List pickNames = new ArrayList(); // List of String;

  private List pickValues = new ArrayList(); // List of String;

  private String defaultValue;

  private boolean required;

  public boolean isQuoted() {
    return quoted != 1;
  }

  public boolean isDate() {
    return quoted == 2;
  }

  public String getDefaultValue() {
    return defaultValue;
  }

  public void setDefaultValue(String defaultValue) {
    this.defaultValue = defaultValue;
  }

  public boolean isHaving() {
    return having;
  }

  public void setHaving(boolean having) {
    this.having = having;
  }

  public String getLeft() {
    return left;
  }

  public void setLeft(String left) {
    this.left = left;
  }

  public String getOperator() {
    return operator;
  }

  public void setOperator(String operator) {
    this.operator = operator;
  }

  public List getPickNames() {
    return pickNames;
  }

  public void setPickNames(List pickNames) {
    this.pickNames = pickNames;
  }

  public List getPickValues() {
    return pickValues;
  }

  public void setPickValues(List pickValues) {
    this.pickValues = pickValues;
  }

  public int getQuoted() {
    return quoted;
  }

  public void setQuoted(int quoted) {
    this.quoted = quoted;
  }

  public boolean isRequired() {
    return required;
  }

  public void setRequired(boolean required) {
    this.required = required;
  }

  public List getRights() {
    return rights;
  }

  public void setRights(List rights) {
    this.rights = rights;
  }

  private QueryDefinition query;

  public QueryDefinition getQuery() {
    return query;
  }

  public void setQuery(QueryDefinition query) {
    this.query = query;
  }

  public String interpretDefaultValue(String text) {
    if (text == null || text.trim().length() == 0)
      return null;
    if ("今天".equals(text)) {
      return QTConstants.SDFyMd.format(new Date());
    } else if ("昨天".equals(text)) {
      return QTConstants.SDFyMd.format(StringUtil.addDate(new Date(), Calendar.DATE, -1));
    } else if ("月初".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("月末".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      c.add(Calendar.MONTH, 1);
      c.add(Calendar.DATE, -1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上月初".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      c.add(Calendar.MONDAY, -1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上月末".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      c.add(Calendar.DATE, -1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("下月初".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      c.add(Calendar.MONTH, 1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("下月末".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_MONTH, 1);
      c.add(Calendar.MONTH, 2);
      c.add(Calendar.DATE, -1);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周日".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周一".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周二".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.TUESDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周三".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.WEDNESDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周四".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.THURSDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周五".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本周六".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.SATURDAY);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周日".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周一".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周二".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.TUESDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周三".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.WEDNESDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周四".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.THURSDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周五".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.FRIDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("上周六".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.set(Calendar.DAY_OF_WEEK, Calendar.SATURDAY);
      c.add(Calendar.DATE, -7);
      return QTConstants.SDFyMd.format(c.getTime());
    } else if ("本年".equals(text)) {
      return QTConstants.SDFyyyy.format(new Date());
    } else if ("本年月".equals(text)) {
      return QTConstants.SDFyyyyMM.format(new Date());
    } else if ("上年".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.add(Calendar.YEAR, -1);
      return QTConstants.SDFyyyy.format(c.getTime());
    } else if ("上年月".equals(text)) {
      Calendar c = Calendar.getInstance();
      c.setTime(new Date());
      c.add(Calendar.YEAR, -1);
      return QTConstants.SDFyyyyMM.format(c.getTime());
    }
    return null;
  }
}
